package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.response.CourseConsumptionRate;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CourseConsumptionRateMapper {

    /**
     * 消课率列表
     */
    @Select({"<script>",
            "SELECT teacher_id,school_id,",
            "getStaffNameForId ( teacher_id ) AS teacher_name,",
            "getSchoolName ( school_id ) AS school_name,",
            "IFNULL(SUM( buckle_class_house ), 0 ) buckle_class_house_all,",
            "IFNULL(SUM( answer_num ), 0 ) AS answer_num_all, ",
            "IFNULL( SUM( buckle_class_house_now ), 0 ) AS buckle_class_house_now, ",
            "COUNT( student_id ) AS student_count ",
            "FROM (",
            "SELECT",
            "t1.school_id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.student_id,",
            "t2.teacher_id,",
            "SUM( t3.buckle_class_house / 3 ) AS buckle_class_house,",
            "getStaffNameForId ( t2.teacher_id ) AS teacher_name,",
            "15 AS answer_num, ",
            "SUM( CASE WHEN t3.school_date BETWEEN '${startDate}' AND '${endDate}' THEN 1 END ) AS buckle_class_house_now ",
            "FROM ",
            "student_grade AS t1 ",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "INNER JOIN record_course AS t3 ON t1.year_part = t3.year_part ",
            "AND t1.quarter_num = t3.`quarter` ",
            "AND t1.student_id = t3.student_id ",
            "AND t1.subjects_id = t3.subjects_id ",
            "AND t1.lesson_type = t3.lesson_type ",
            "AND t1.deleted = t3.deleted ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t1.school_id IN (${schoolId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "AND t1.class_type_id_new IN (${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t3.subjects_id IN (${subjectsId})",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND t2.year_class_id IN (${yearClassId})",
            "</when>",
            "<if test= 'type == 3'>",
            "and t2.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242) ",
            "</if>",
            "AND t1.is_last = 1 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.deleted = 0 ",
            "AND t3.is_deduct_house = 1 ",
            "AND t3.is_deduct_money = 1 ",
            "AND ((t3.`status` = 1 AND t3.buckle_class_house > 0 ) OR (t3.`status` = 2 AND t3.is_deduct_house = 1 AND t3.is_deduct_money = 1 AND t3.buckle_class_house > 0 AND t3.deduct_type = 4 )) ",
            "GROUP BY t1.student_id,t1.subjects_id ",
            ") AS t ",
            "GROUP BY ",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id ",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
            "school_id",
            "</if>",
            "</script>"})
    public abstract List<CourseConsumptionRate> getCourseConsumptionRateList(@Param("type") String type, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("subjectsId") String subjectsId);

    /**
     * 详情 暂时不需要
     */
    @Select({"<script>",
            "SELECT",
            "getSchoolName ( t1.school_id ) AS school_name, ",
            "getSubjectsName ( t1.subjects_id ) AS subjects_name,",
            "t3.student_name,",
            "getStaffNameForId ( t2.teacher_id ) AS teacher_name,",
            "SUM( t3.buckle_class_house / 3 ) AS buckle_class_house ",
            "FROM ",
            "student_grade AS t1 ",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "INNER JOIN record_course AS t3 ON t1.year_part = t3.year_part ",
            "AND t1.quarter_num = t3.`quarter` ",
            "AND t1.student_id = t3.student_id ",
            "AND t1.subjects_id = t3.subjects_id ",
            "AND t1.lesson_type = t3.lesson_type ",
            "AND t1.deleted = t3.deleted ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t1.school_id IN (${schoolId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "AND t1.class_type_id_new IN (${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t3.subjects_id IN (${subjectsId})",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND t2.year_class_id IN (${yearClassId})",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t2.school_id = ${teacherId}",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t3.school_date BETWEEN '${startDate}' AND '${endDate}'",
            "</when>",
            "AND t1.is_last = 1 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.deleted = 0 ",
            "AND t3.is_deduct_house = 1 ",
            "AND t3.is_deduct_money = 1 ",
            "AND ((t3.`status` = 1 AND t3.buckle_class_house > 0 ) OR (t3.`status` = 2 AND t3.is_deduct_house = 1 AND t3.is_deduct_money = 1 AND t3.buckle_class_house > 0 AND t3.deduct_type = 4 )) ",
            "GROUP BY t1.student_id,t1.subjects_id ",
            "</script>"})
    public abstract List<CourseConsumptionRate> getCourseConsumptionRateDetails(@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("teacherId") String teacherId,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("subjectsId") String subjectsId);


}
